﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ProductShowView]'))
DROP VIEW [dbo].[ProductShowView]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BasketView]'))
DROP VIEW [dbo].[BasketView]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BasketItemView]'))
DROP VIEW [dbo].[BasketItemView]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BasketItemView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BasketItemView] AS

SELECT
	BasketItem.Id,
	BasketItem.BasketId,
	BasketItem.Quantity,
	Product.Id AS ProductId,
	Product.Name AS ProductName,
	Product.Price AS ProductPrice
FROM
	BasketItem
	INNER JOIN Product ON BasketItem.ProductId = Product.Id
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ProductShowView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ProductShowView] AS

SELECT
	Id,
	[Name],
	Price
FROM
	dbo.Product
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BasketView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BasketView] AS

SELECT
	Basket.Id AS Id,
	Shopper.Id AS ShopperId,
	SUM(Product.Price * BasketItem.Quantity) AS SubTotal
FROM
	Basket
	INNER JOIN Shopper ON Basket.Id = Shopper.BasketId
	INNER JOIN BasketItem ON Basket.Id = BasketItem.BasketId
	INNER JOIN Product ON BasketItem.ProductId = Product.Id
GROUP BY
	Basket.Id,
	Shopper.Id
'
GO
